﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace HZ.DrugTrade.DAL
{
    public class DAL_InvoicePay
    {
        public static IList<HZ.DrugTrade.Model.Company> GetUnPayCompanyList(string HospitalID, string CompanyName, int pagesize, int currentpage, out int totalcount)     //updated by lhl 2009-09-07
        {
            string sql = string.Format(@"SELECT DISTINCT c.* FROM TB_Company  c INNER JOIN TB_Invoice i ON i.CompanyID_PS = c.CompanyID
WHERE InvoicePaySerialID IS NULL AND AuditStatus=1 AND i.HospitalID = '{0}' AND c.CompanyName LIKE '%{1}%' ORDER BY CompanyID", HospitalID, CompanyName);
            //updated by lhl 2009-09-09 增加一个限制条件：AuditStatus=1 企业只有将发票提交给医疗机构，才能将该企业作为待结算的企业
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize);
            pdes.CurrentPage = currentpage;
            totalcount = pdes.TotalCount;
            DataTable dt = pdes.GetSpecailPage();
            //return HZ.Data.DAL_Helper.GetCompaniesFromDataTable(dt);
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.Company>(dt);

        }

        /// <summary>
        /// 获取历史有过交易记录的企业
        /// </summary>
        /// <param name="HospitalID"></param>
        /// <param name="CompanyName"></param>
        /// <returns></returns>
        public static DataTable GetHistroyTradeCompany(string HospitalID, string CompanyName, string CompaynId, int pagesize, int currentpage, out int totalcount)
        {
            string sql = string.Format(@"SELECT count(InvoicePaySerialID) AS PayCount,MIN(CompanyName) AS CompanyName,CompanyID_PS,sum(PayAmount) AS                                            payAmount,sum(ActualAmount) AS actualAmount,sum(PayAmount-ActualAmount) AS oweAmount 
                                        FROM View_HistroyTradeCompany 
                                        WHERE HospitalID LIKE '%{0}%' AND CompanyName LIKE '%{1}%' AND CompanyID_PS LIKE '%{2}%'AND PayStatus=1
                                        GROUP BY CompanyID_PS ORDER BY CompanyID_PS", HospitalID, CompanyName, CompaynId);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize);
            pdes.CurrentPage = currentpage;
            totalcount = pdes.TotalCount;
            DataTable dt = pdes.GetSpecailPage();
            return dt;
        }

        /// <summary>
        /// 获取历史有过交易记录的医疗机构
        /// </summary>
        /// <param name="HospitalID"></param>
        /// <param name="CompanyName"></param>
        /// <returns></returns>
        public static DataTable GetHistroyTradeHospital(string HospitalID, string HospitalName, string CompaynId, int pagesize, int currentpage, out int totalcount)
        {
            string sql = string.Format(@"SELECT count(InvoicePaySerialID) AS PayCount,MIN(HospitalName) AS HospitalName,HospitalID,sum(PayAmount) AS                                            payAmount,sum(ActualAmount) AS actualAmount,sum(PayAmount-ActualAmount) AS oweAmount 
                                        FROM View_HistroyTradeCompany 
                                        WHERE HospitalID LIKE '%{0}%' AND HospitalName LIKE '%{1}%' AND CompanyID_PS LIKE '%{2}%'AND PayStatus=1
                                        GROUP BY HospitalID ORDER BY HospitalID", HospitalID, HospitalName, CompaynId);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize);
            pdes.CurrentPage = currentpage;
            totalcount = pdes.TotalCount;
            DataTable dt = pdes.GetSpecailPage();
            return dt;
        }

        public static int DeleteInvoicePay(HZ.DrugTrade.Model.InvoicePay InvoicePay)
        {

            string sql = string.Format(@"SELECT PayStatus FROM 
TB_InvoicePay WHERE InvoicePaySerialID = '{0}'", InvoicePay.InvoicePaySerialID);
            int PayStatus = int.Parse(HZ.Data.DbHelperFactory.Create().ExecuteScalar(sql).ToString());
            if (PayStatus == 1)
            {
                return 0;
            }
            else
            {
                var h = HZ.Data.DbHelperFactory.Create();
                try
                {
                    h.BeginTrans();
                    sql = string.Format("UPDATE TB_Invoice SET InvoicePaySerialID = NULL WHERE InvoicePaySerialID = '{0}'", InvoicePay.InvoicePaySerialID);
                    h.ExecuteNonQuery(sql);
                    sql = string.Format("DELETE FROM TB_InvoicePay WHERE InvoicePaySerialID = '{0}'", InvoicePay.InvoicePaySerialID);
                    h.ExecuteNonQuery(sql);
                    h.CommitTrans();

                }
                catch (Exception ee)
                {
                    HZ.Common.log4netManager.LogDebug("DAL_InvoicePay", ee.Message, ee);
                    h.RollTrans();
                    throw;
                }
                return 1;
            }
        }


        /// <summary>
        /// 更新结算单
        /// </summary>
        /// <param name="purchaseorder">结算单</param>
        /// <returns></returns>
        public static int InvoicePayUpdate(HZ.DrugTrade.Model.InvoicePay invoicepay)
        {
            return HZ.Data.DAL_Helper.UpdateModel<HZ.DrugTrade.Model.InvoicePay>(invoicepay);

        }
        /// <summary>
        /// 更新结算单
        /// </summary>
        /// <param name="purchaseorder">结算单</param>
        /// <returns></returns>
        //public static int InvoicePayUpdate(HZ.DrugTrade.Model.InvoicePay invoicepay)
        //{
        //    IDataParameter[] paras = new IDataParameter[] { 
        //         new SqlParameter("@in_InvoicePaySerialID",invoicepay.InvoicePaySerialID),
        //         new SqlParameter("@in_InvoicePayTime",invoicepay.InvoicePayTime),
        //         //new SqlParameter("@in_PayAmount",invoicepay.PayAmount),
        //         // new SqlParameter("@in_ActualAmount",invoicepay.ActualAmount),
        //          //new SqlParameter("@in_PayStatus",invoicepay.PayStatus),
        //          new SqlParameter("@in_InvoicePayName",invoicepay.InvoicePayName),
        //         new SqlParameter("@in_InvoicePayNo",invoicepay.InvoicePayNo),
        //         new SqlParameter("@in_Remarks",invoicepay.Remarks),
        //          new SqlParameter("@out_Success",SqlDbType.Int)
        //    };
        //    paras[paras.Length - 1].Direction = ParameterDirection.Output;
        //    HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(CommandType.StoredProcedure, "usp_InvoicePay_Update", paras);
        //    return (int)paras[paras.Length - 1].Value;

        //    //HZ.Data.SQLHelper h = new HZ.Data.SQLHelper();
        //    //try
        //    //{
        //    //    h.BeginTrans();
        //    //    string sql = string.Format("UPDATE [TB_InvoicePay] SET ActualAmount ='{0}',InvoicePayNo ='{1}' WHERE InvoicePaySerialID='{2}'", invoicepay.ActualAmount, invoicepay.InvoicePayNo, invoicepay.InvoicePaySerialID);
        //    //    h.ExecuteNonQuery(HZ.Data.DAL_Helper.GetUpdateSql(invoicepay));
        //    //    h.ExecuteNonQuery(sql);
        //    //    h.CommitTrans();

        //    //}
        //    //catch (Exception ee)
        //    //{
        //    //    HZ.Common.log4netManager.LogDebug("DAL_InvoicePay", ee.Message, ee);
        //    //    h.RollTrans();
        //    //    throw;
        //    //}
        //    //return 1;
        //}

        public static int UpdateInvoicePay(HZ.DrugTrade.Model.InvoicePay InvoicePay, IList<HZ.DrugTrade.Model.Invoice> invoicelist)
        {

            var h = HZ.Data.DbHelperFactory.Create();
            try
            {
                h.BeginTrans();
                string sql = string.Format("UPDATE TB_Invoice SET InvoicePaySerialID = NULL WHERE InvoicePaySerialID = '{0}'", InvoicePay.InvoicePaySerialID);
                h.ExecuteNonQuery(sql);
                for (int i = 0; i < invoicelist.Count; i++)
                {
                    string strsql = string.Format(@"select MODIFYTIME from TB_Invoice where InvoiceSerialID = '{0}'", invoicelist[i].InvoiceSerialID);
                    DateTime time = Convert.ToDateTime(h.ExecuteScalar(strsql));
                    if (time.ToString("yyyy-MM-dd") != DateTime.Now.ToString("yyyy-MM-dd") || time == null)
                    {
                        sql = string.Format(" UPDATE TB_Invoice SET InvoicePaySerialID = '{0}',MODIFYTIME = sysdate,MODIFYSTATUS =1 WHERE InvoiceSerialID = '{1}'", InvoicePay.InvoicePaySerialID, invoicelist[i].InvoiceSerialID);
                    }
                    else
                    {
                        sql = string.Format(" UPDATE TB_Invoice SET InvoicePaySerialID = '{0}' WHERE InvoiceSerialID = '{1}'", InvoicePay.InvoicePaySerialID, invoicelist[i].InvoiceSerialID);
                    }
                    HZ.Data.DAL_Helper.OperateTableSQL<HZ.DrugTrade.Model.InvoicePay>(InvoicePay, HZ.Data.TableOperator.Update, h);
                    h.ExecuteNonQuery(sql);
                }
                
                //sql = string.Format(" UPDATE TB_Invoice SET InvoicePaySerialID = '{0}' WHERE InvoiceSerialID IN ({1})", InvoicePay.InvoicePaySerialID, HZ.Data.DAL_Helper.GetModelFieldArrayString(invoicelist, "InvoiceSerialID", ",", true));
                //HZ.Data.DAL_Helper.OperateTableSQL<HZ.DrugTrade.Model.InvoicePay>(InvoicePay, HZ.Data.TableOperator.Update, h);
                //h.ExecuteNonQuery(sql);
                h.CommitTrans();

            }
            catch (Exception ee)
            {
                HZ.Common.log4netManager.LogDebug("DAL_InvoicePay", ee.Message, ee);
                h.RollTrans();

                throw;
            }
            return 1;
        }
        /// <summary>
        /// 查询结算单状态
        /// add by Meng 2013-03-06
        /// </summary>
        /// <param name="qryWhere"></param>
        /// <returns></returns>
        public static int SelectInvoiceState(HZ.DrugTrade.Model.InvoicePay qryWhere)
        {
            string strsql = string.Format(@" select count(*) from tb_invoicepay where paystatus = 1 and InvoicePaySerialID = '{0}'",qryWhere.InvoicePaySerialID);
            return Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteScalar(strsql));
        }
        /// <summary>
        /// 查询发票单状态
        /// add by Meng 2013-03-06
        /// </summary>
        /// <param name="qryWhere"></param>
        /// <returns></returns>
        public static int SelectInvoiceStatus(string InvoiceSerialID)
        {
            string strsql = string.Format(@" select count(*) from tb_invoice where AuditStatus = 0 and InvoiceSerialID in({0})", InvoiceSerialID);
            return Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteScalar(strsql));
        }
        /// <summary>
        /// 查询结算单状态
        /// add by Meng 2013-03-06
        /// </summary>
        /// <param name="qryWhere"></param>
        /// <returns></returns>
        public static int SelectInvoiceStates(string InvoicePaySerialID)
        {
            string strsql = string.Format(@" select count(*) from tb_invoicepay where paystatus = 1 and InvoicePaySerialID = '{0}'",InvoicePaySerialID);
            return Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteScalar(strsql));
        }
    }
}
